﻿using DBUtil.Attributes;
using DotNetCommon.Extensions;
using NUnit.Framework;
using Shouldly;
using System.Collections.Generic;
using System.ComponentModel.DataAnnotations.Schema;
using System.Linq;

namespace Test.MySql.JsonTests.ListAndArray
{
    [TestFixture]
    internal class IEnumerableTestsSimple : TestBase
    {
        #region model
        [Table("test")]
        public class Person
        {
            [PrimaryKey, Identity]
            [Column("id")]
            public int Id { get; set; }
            [JsonMap]
            public IEnumerable<int> p_ints { get; set; }
            [JsonMap]
            public IEnumerable<string> p_strings { get; set; }
            [JsonMap]
            public IEnumerable<bool> p_bools { get; set; }
        }
        #endregion

        [SetUp]
        public void SetUp()
        {
            DropTableIfExist("test");
            db.ExecuteSql("create table test(id int primary key auto_increment,p_ints json,p_strings json,p_bools json)");
            db.ExecuteSql("""
                insert into test(p_ints,p_strings,p_bools) values
                    ('[1,3,2]','["ab","ac","bcd"]','[true,false,true]'),
                    ('[3,4,2]','["ab","acd","bcd"]','[false,false,true]'),
                    ('[1,2,3]','["ab","ac","bcd"]','[false,true,true]');
                """);
        }

        [Test]
        public void TestInsert()
        {
            var insert = db.Insert<Person>().SetEntity(new Person { p_bools = [true, false, true], p_ints = [1, 3, 2], p_strings = ["ab", "ac", "bcd"] });
            var sql = insert.ToSql();
            sql.ShouldBe("""
                insert into `test`(`p_ints`,`p_strings`,`p_bools`) values('[1,3,2]','["ab","ac","bcd"]','[true,false,true]');
                """);
            var p = insert.ExecuteInserted();
            var json = p.ToJson();
            json.ShouldBe("""{"Id":4,"p_ints":[1,3,2],"p_strings":["ab","ac","bcd"],"p_bools":[true,false,true]}""");
        }

        [Test]
        public void TestSelect()
        {
            var select = db.Select<Person>().Where(i => i.p_ints.ElementAt(1) > 2);
            var sql = select.ToSqlList();
            sql.ShouldBe("""
                select t.`id` `Id`,t.`p_ints`,t.`p_strings`,t.`p_bools`
                from `test` t
                where (json_value(t.`p_ints`,'$[1]' returning signed)) > 2;
                """);
            var list = select.ToList();
            var json = list.ToJson();
            json.ShouldBe("""[{"Id":1,"p_ints":[1,3,2],"p_strings":["ab","ac","bcd"],"p_bools":[true,false,true]},{"Id":2,"p_ints":[3,4,2],"p_strings":["ab","acd","bcd"],"p_bools":[false,false,true]}]""");
        }
        [Test]
        public void TestSelect2()
        {
            var select = db.Select<Person>().Where(i => !i.p_bools.ElementAt(1));
            var sql = select.ToSqlList();
            sql.ShouldBe("""
                select t.`id` `Id`,t.`p_ints`,t.`p_strings`,t.`p_bools`
                from `test` t
                where not (json_value(t.`p_bools`,'$[1]' returning signed));
                """);
            var list = select.ToList();
            var json = list.ToJson();
            json.ShouldBe("""[{"Id":1,"p_ints":[1,3,2],"p_strings":["ab","ac","bcd"],"p_bools":[true,false,true]},{"Id":2,"p_ints":[3,4,2],"p_strings":["ab","acd","bcd"],"p_bools":[false,false,true]}]""");
        }

        [Test]
        public void TestSelect3()
        {
            //应该属于 .Any() 的case吧, 转移出去
            var select = db.Select<Person>().Where(i => i.p_strings.Any());
            var sql = select.ToSqlList();
            sql.ShouldBe("""
                select t.`id` `Id`,t.`p_ints`,t.`p_strings`,t.`p_bools`
                from `test` t
                where json_length(t.`p_strings`)>0;
                """);
        }
        [Test]
        public void TestUpdate()
        {
            var update = db.Update<Person>().SetExpr(i => new Person
            {
                Id = 1,
                p_bools = new List<bool> { false, false, true }
            });
            var sql = update.ToSql();
            sql.ShouldBe("""
                update `test` set
                    `p_bools` = '[false,false,true]'
                where `id` = 1;
                """);
            var r = update.ExecuteAffrows();
            r.ShouldBe(1);
            var p = db.Select<Person>().Where(i => i.Id == 1).FirstOrDefault();
            var json = p.ToJson();
            json.ShouldBe("""{"Id":1,"p_ints":[1,3,2],"p_strings":["ab","ac","bcd"],"p_bools":[false,false,true]}""");
        }
    }
}
